一文搞懂MySQL加锁机制! | 您所在的位置:网站首页 › for update 怎么解锁 › 一文搞懂MySQL加锁机制! |
这是我参与2022首次更文挑战的第2天,活动详情查看:2022首次更文挑战 前言在数据库中设计锁的目的是为了处理并发问题,在并发对资源进行访问时,数据库要合理控制对资源的访问规则。 而锁就是用来实现这些访问规则的一个数据结构。 在对数据并发操作时,没有锁可能会引起数据的不一致,导致更新丢失。 乐观锁: 对于出现更新丢失的可能性比较乐观,先认为不会出现更新丢失,在最后更新数据时进行比较。 CREATE TABLE `t` ( `id` int(11) NOT NULL, `value` int(11) DEFAULT NULL, `version` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB ; insert into t values(1,1,1); select id,value,version from t where id=1; update t set value=2,version=version+1 where id=1 and version=1;**悲观锁: ** 对于出现更新丢失的可能性比较悲观,在查询时就加锁,保证不被其他事务操作;可通过select...fot update实现。 select * from t where id = 1 for update; 共享锁(S锁)和排他锁(X锁)共享锁(shared lock)是指多个事务之间可以共享锁资源,一般都是在读取数据时添加,也称为读锁(read lock)。 select * from t where id = 1 lock in share mode;排它锁( exclusive lock,X锁),也称为写锁(write lock)。 当事务A对数据添加上X锁后,其他事务则不能再对该数据添加任何锁,直到事务A释放数据上的X锁。 增、删、改都会对数据添加X锁,在查询语句中使用for update也会添加X锁。 S锁X锁S锁√×X锁×× 按加锁粒度区分 全局锁顾名思义,全局锁是对整个数据库加锁,加锁之后整个库对其他事务都不能进行写操作。MySQL中提供一种添加全局读锁的方式,命令是:flush tables with read lock(FTWRL)。 -- 加全局读锁 flush tables with read lock; -- 解锁 unlock tables;使用场景:全库逻辑备份。 但是使用全局锁进行备份有以下问题: 如果在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆; 如果在从库上备份,那么备份期间从库不能执行主库同步过来的binlog,会导致主从延迟。如果全库中所有的表都是innoDB引擎或其他支持事务的存储引擎,可以使用官方的备份工具mysqldump。 当mysqldump使用参数–single-transaction的时候,导数据之前就会启动一个事务,来确保拿到一致性视图。因为有MVCC的支持,这个过程中数据是可以正常更新的。 如果库中存在MyISAM这种不支持事务的存储引擎,则不能使用mysqldump。 使用set global readonly=true是否可行? 不可以使用set global readonly=true让全库只读后做逻辑备份。主要有两个原因: read-only一般会用来区分主库和备库,修改global变量影响较大,不建议修改。 异常处理机制不同,执行FTWRL命令后如果客户端异常断开连接服务器会自动释放全局锁。但是将read-ony设置为true之后则会永久生效,如果客户端异常断开,数据库会一直保持read-only状态。 表级锁(表锁和MDL锁)MySQL里面表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)。 表锁 lock tables ... read; lock tables ... write; -- 解锁 unlock tables;使用表锁的开销相对较小,加锁快,不会产生死锁;但是加锁粒度大,发生锁冲突的概率更高,并发度更低。 在innoDB存储引擎中不推荐使用表锁,只有在没有事务支持的存储引擎中才会使用,如MyISAM。 元数据锁(MDL) 元数据锁是在MySQL5.5中引入的,MDL不需要显式添加,在对表数据做增删改查操作时添加MDL读锁,在对表进行DDL操作时添加MDL写锁。 元数据锁是为了保证在多个事务操作同一张表时表的元数据一致性。 如果没有元数据锁会存在什么问题呢? 事务隔离问题: 比如在可重复读隔离级别下,会话A在两次查询期间,会话B对表结构做了修改,两次查询结果就会不一致,无法满足可重复读的要求。 数据同步问题: 比如会话A执行了多条更新语句期间,另外一个会话B做了表结构变更并且先提交,就会导致备库在重做时,先重做alter table语句,再重做update语句时就会出现复制错误的现象。 MDL读锁之间不互斥,因为一张表可以支持多个事务同时增删改查,读锁和写锁、写锁和写锁之间互斥,用来保证对表结构变更的安全性。 在对表执行DDL时,会导致所有的增删改查阻塞。所以在对表字段进行修改或增加字段时,一定要特别小心。 一般我们在对大数据量表做DDL时都会格外注意,以免对线上业务造成影响。但是对小表做DDL操作时同样要小心,比如以下场景: 事务A先启动,这时会对表t加一个MDL读锁; 然后事务B要对表t增加字段,这是需要获取一个MDL写锁,但是由于这时事务A还没有提交,所以MDL读锁没有释放,所以事务B会被阻塞; 如果仅仅是事务B阻塞倒也没什么关系,顶多是DDL晚点执行;但是在这之后的所有对表t的增删改查都会被阻塞,导致表t不能执行任何读写操作。 意向锁意向锁是加在表级别的一个锁,分为意向共享锁(IS锁)和意向排它锁(IX锁)。 意向锁,顾名思义,就是指明接下来要做的是一个什么类型的操作。 意向共享锁(IS):在准备给表数据添加一个S锁时,需要先获得该表的IS锁。 意向排他锁(IX):在准备给表数据添加一个X锁时,需要先获得该表的IX锁。
之所以有意向锁的存在,所以在上面的例子中: 意向锁的出现还有一个主要原因是为了在支持不同粒度锁时,能有更高的效率。 事务A对表T中的某一数据行添加了行锁,这时事务B要对表T添加表锁,但是在添加之前需要先检查是否有其他事务持有该表的X锁,如果持有则要阻塞; 事务B通过遍历表T中的所有行是否有锁,这样判断效率很低,非常耗时。 而意向锁因为是表级别的锁,在事务A在更新数据添加行锁之前,会在表级别由数据库自动添加一个IX锁,那么当事务B在需要获取X锁时,只需要检查表级别是否有IX锁,如果有IX锁代表当前有其他事务正在对表或者表中数据执行写操作,不能加锁成功。 行锁MySQL中的行锁是在存储引擎层实现的,并不是所有的存储引擎都支持,比如MyISAM引擎中就没有行锁。 行锁顾名思义,是在数据行上添加锁,比如事务A要更新一行数据,先添加了行锁,然后事务B也要更新该行数据,则必须等事务A释放行锁之后才能更新。 行锁的加锁和解锁时机 在InnoDB事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。这个就是两阶段锁协议。 begin; update t set value = value + 1 where id = 1; update t set value = value + 1 where id = 2; begin ; update t set value = value + 1 where id = 1;因此,如果你的事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。 间隙锁间隙锁,锁的就是两个值之间的空隙。 我们都知道每个技术的出现都是为了解决某个问题,那么间隙锁又是为了解决什么问题呢? 假设没有间隙锁,会怎么样,我们来看下面的例子,以下内容都是在可重复读隔离级别的前提下。 有如下一张表: CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE=InnoDB; insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20);假设我们要执行如下SQL,会如何进行加锁和解锁? begin; select * from t where d=5 for update; commit;比较好理解的是,这个语句会命中d=5的这一行,对应的主键id=5,因此在select 语句执行完成后,id=5这一行会加一个写锁,而且由于两阶段锁协议,这个写锁会在执行commit语句的时候释放。 由于字段d上没有索引,因此这条查询语句会做全表扫描。那么,其他被扫描到的,但是不满足条件的5行记录上,会不会被加锁呢? 在事务A中执行了3次查询,都是通过for update获取写锁,并且是当前读。 假设只有id=5这一行加锁,那么三个查询的执行结果如下: Q1返回结果为(5,5,5); Q2返回结果为(0,0,5),(5,5,5); Q3返回结果为(0,0,5)(1,1,5)(5,5,5);那么Q3的结果中查询到id=1的数据,这个现象被称为“幻读”。 这破坏了事务A中select * from t where d=5 fot update;要把所有d=5的数据锁住的语义。 其次,会存在数据一致性问题。 如果在事务B中将binlog拿到备库执行会得到不一样的结果。 实际验证一下,得到结果并不是只对id=5这一行加锁,并且对所有的间隙也加了锁。这样就保证不能再插入新的数据。 next-key lock(临键锁)间隙锁和行锁合称next-key lock,每个next-key lock是前开后闭区间。也就是说,我们的表t初始化以后,如果用select * from t where for update要把整个表所有记录锁起来,就形成了7个next-key lock,分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。 间隙锁和临建锁的目的都是用来解决可重复读的问题,如果在读提交级别,间隙锁和临建锁都会失效。 加锁规则MySQL中数据加锁的规则可以归纳为以下三种: 两个原则 加锁的基本单位是next-key lock 查找过程中访问到的对象才会加锁两个优化 索引上的等值查询,给唯一索引加锁的时候,next-key lock退化为行锁 索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock退化为间隙锁一个BUG 唯一索引上的范围查询会访问到不满足条件的第一个值为止 死锁和死锁检测什么是死锁? 在支持并发操作的系统中,不同的线程对资源出现循环依赖,线程之间互相持有对方需要的资源,导致线程都进入无限等待的状态,称之为死锁。 而在数据库中因为有锁机制的存在,同样会导致死锁。比如: 当出现死锁后,有两种处理策略: 直接进入等待,直到连接超时,超时时间可通过innodb_lock_wait_timeout设置。 发起死锁检测,发现死锁后主动回滚死锁中的一个事务,让其他事务正常执行。将参数innodb_deadlock_detect设置为on,表示开启死锁检测。写文不易,需要一点正反馈,喜欢的话点个赞~ 往期推荐文章: 快丢掉BeanUtils吧,MapStruct才是YYDS 🔥 三分钟学会!自动化给她发微信 👩 送春节祝福! 面试官让我设计个LRU缓存,结果... maven中有重复依赖该怎么搞 淦!做一个短链接系统需要考虑这么多 小黑的专栏: 小黑说优质文章合集 【跟着小黑学设计模式】 【并发编程系列】 【Maven系列】 我是小黑,一名在互联网“苟且”的程序员 流水不争先,贵在滔滔不绝 |
CopyRight 2018-2019 实验室设备网 版权所有 |